import pymysql
import csv
from datetime import datetime
from dotenv import load_dotenv
from decimal import Decimal
import os
import getpass  # 导入 getpass 模块，用于隐藏输入的密码
import re

def get_connection():
    return pymysql.connect(
        host="localhost",
        port=3306,  # 确保 port 是整数类型
        user="root",
        password="69296616",
        database="dbkeshe",
        cursorclass=pymysql.cursors.DictCursor
    )

# 一、管理员模块
# 1.1 客户管理
# 添加客户，接收客户信息并将其插入到数据库中
def add_customer(customerName, PID, telephone, address):
    # 后端校验
    if not customerName or not PID or not telephone:
        raise ValueError('客户姓名、身份证号、联系电话不能为空')
    if not re.match(r'^\d{17}[\dXx]$', PID):
        raise ValueError('身份证号格式不正确，必须为18位数字或17位数字加1位数字或X/x')
    if not re.match(r'^\d{11}$', telephone):
        raise ValueError('联系电话格式不正确，必须为11位数字')
    conn = get_connection()
    cursor = conn.cursor()
    # 身份证唯一性校验
    cursor.execute('SELECT COUNT(*) FROM userinfo WHERE PID=%s', (PID,))
    if cursor.fetchone()['COUNT(*)'] > 0:
        conn.close()
        raise ValueError('该身份证号已存在，不能重复开户')
    cursor.execute('''INSERT INTO userinfo (customerName, PID, telephone, address)
                      VALUES (%s, %s, %s, %s)''', (customerName, PID, telephone, address))
    conn.commit()
    print(f"成功添加客户: {customerName}")
    conn.close()

#查看所有客户信息
def view_all_customers():
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('''SELECT * FROM userinfo''')
    rows = cursor.fetchall()
    conn.close()
    print("查看全部用户信息:")
    for row in rows:
        print(row)  # 假设每一行是一个字典，直接打印出来
    return rows

#更新客户信息
def update_customer(customerID, customerName=None, telephone=None, address=None):
    conn = get_connection()
    cursor = conn.cursor()
    if customerName:
        cursor.execute('''UPDATE userinfo SET customerName=%s WHERE customerID=%s''', (customerName, customerID))
    if telephone:
        cursor.execute('''UPDATE userinfo SET telephone=%s WHERE customerID=%s''', (telephone, customerID))
    if address:
        cursor.execute('''UPDATE userinfo SET address=%s WHERE customerID=%s''', (address, customerID))
    conn.commit()
    print(f"成功更新客户: {customerID}")
    conn.close()

#删除客户信息
def delete_customer(customerID):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('''DELETE FROM userinfo WHERE customerID=%s''', (customerID,))
    conn.commit()
    print(f"成功删除客户: {customerID}")
    conn.close()

def get_connection():
    return pymysql.connect(
        host="localhost",
        port=3306,
        user="root",
        password="69296616",
        database="dbkeshe",
        cursorclass=pymysql.cursors.DictCursor
    )

def deposit_to_customer():
    conn = get_connection()
    cursor = conn.cursor()

    # 管理员输入客户ID
    customerID = input("管理员请输入客户ID进行存款操作: ")

    # 确认客户ID是否有效
    cursor.execute('''SELECT cardmima, balance FROM cardinfo WHERE customerID=%s''', (customerID,))
    result = cursor.fetchone()

    if not result:
        print(f"客户ID {customerID} 不存在，存款操作失败！")
        conn.close()
        return

    # 用户输入密码
    input_cardmima = getpass.getpass("请输入客户卡密码进行存款操作: ")

    # 验证密码
    stored_cardmima = result['cardmima']
    if stored_cardmima != input_cardmima:
        print("卡密码错误，存款操作失败！")
        conn.close()
        return

    # 输入存款金额
    while True:
        try:
            amount = Decimal(input("请输入存款金额: "))
            if amount <= 0:
                print("存款金额必须大于零！")
                continue
            break
        except ValueError:
            print("请输入有效的金额！")

    # 更新余额
    current_balance = result['balance']
    new_balance = current_balance + amount  # 确保类型一致
    cursor.execute('''UPDATE cardinfo SET balance=%s WHERE customerID=%s''', (new_balance, customerID))
    conn.commit()
    print(f"客户 {customerID} 存款成功，当前余额: {new_balance}")

    conn.close()

def withdraw_from_customer():
    conn = get_connection()
    cursor = conn.cursor()

    # 管理员输入客户ID
    customerID = input("管理员请输入客户ID进行取款操作: ")

    # 确认客户ID是否有效
    cursor.execute('''SELECT cardmima, balance FROM cardinfo WHERE customerID=%s''', (customerID,))
    result = cursor.fetchone()

    if not result:
        print(f"客户ID {customerID} 不存在，取款操作失败！")
        conn.close()
        return

    # 用户输入密码
    input_cardmima = getpass.getpass("请输入客户卡密码进行取款操作: ")

    # 验证密码
    stored_cardmima = result['cardmima']
    if stored_cardmima != input_cardmima:
        print("卡密码错误，取款操作失败！")
        conn.close()
        return

    # 输入取款金额
    while True:
        try:
            amount = Decimal(input("请输入存款金额: "))
            if amount <= 0:
                print("取款金额必须大于零！")
                continue
            break
        except ValueError:
            print("请输入有效的金额！")

    # 检查余额是否足够
    current_balance = result['balance']
    if current_balance >= amount:
        new_balance = current_balance - amount  # 确保类型一致
        cursor.execute('''UPDATE cardinfo SET balance=%s WHERE customerID=%s''', (new_balance, customerID))
        conn.commit()
        print(f"客户 {customerID} 取款成功，当前余额: {new_balance}")
    else:
        print(f"客户 {customerID} 余额不足，取款失败！")

    conn.close()

# 导出客户数据到 CSV 文件
def export_customers_to_csv():
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('''SELECT * FROM userinfo''')
    rows = cursor.fetchall()
    conn.close()

    # 定义 CSV 文件路径
    csv_file_path = r"D:\UNIVERSITY WORK\大二下\DBkeshe\bank_system\客户管理.csv"

    # 写入 CSV 文件
    with open(csv_file_path, mode='w', newline='', encoding='utf-8') as file:
        writer = csv.DictWriter(file, fieldnames=rows[0].keys())
        writer.writeheader()
        writer.writerows(rows)

    print(f"客户数据已成功导出到 {csv_file_path}")

def verify_customer_login(cardID, password):
    """用银行卡号和密码验证登录"""
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('''SELECT * FROM cardinfo WHERE cardID = %s AND cardmima = %s''', (cardID, password))
    result = cursor.fetchone()
    conn.close()
    return result is not None

# 1.2 银行卡管理
#添加银行卡
def add_bankcard(curID, savingID, openMoney, customerID):
    # 校验
    if not customerID or not savingID or not openMoney:
        raise ValueError('客户编号、存款类型、开户金额不能为空')
    try:
        openMoney = float(openMoney)
    except:
        raise ValueError('开户金额必须为数字')
    if openMoney < 1:
        raise ValueError('开户金额必须大于等于1元')
    conn = get_connection()
    cursor = conn.cursor()
    # 校验客户编号是否存在
    cursor.execute('SELECT COUNT(*) FROM userinfo WHERE customerID=%s', (customerID,))
    if cursor.fetchone()['COUNT(*)'] == 0:
        conn.close()
        raise ValueError('客户编号不存在')
    # 校验存款类型是否存在（改为deposit表）
    cursor.execute('SELECT COUNT(*) FROM deposit WHERE savingID=%s', (savingID,))
    if cursor.fetchone()['COUNT(*)'] == 0:
        conn.close()
        raise ValueError('存款类型编号不存在')
    # 自动生成唯一卡号
    prefix = '10103576'
    while True:
        suffix = ''.join([str(i) for i in __import__('random').sample(range(10), 8)])
        cardID = prefix + suffix
        cursor.execute('SELECT COUNT(*) FROM cardinfo WHERE cardID=%s', (cardID,))
        if cursor.fetchone()['COUNT(*)'] == 0:
            break
    # 默认值
    cardmima = '888888'
    curID = 'RMB'
    IsReportLoss = '否'
    openDate = datetime.now().strftime('%Y-%m-%d')
    balance = openMoney
    # 插入
    cursor.execute('''INSERT INTO cardinfo (cardID, curID, savingID, openDate, openMoney, balance, cardmima, IsReportLoss, customerID)
                      VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)''',
                   (cardID, curID, savingID, openDate, openMoney, balance, cardmima, IsReportLoss, customerID))
    conn.commit()
    print(f"成功添加银行卡: {cardID}")
    conn.close()

#查看全部银行卡信息
def view_all_bankcards():
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('''SELECT * FROM cardinfo''')
    rows = cursor.fetchall()
    conn.close()
    print("所有银行卡信息:")
    for row in rows:
        print(row)
    return rows


#删除银行卡信息
def delete_bankcard(cardID):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('''DELETE FROM cardinfo WHERE cardID=%s''', (cardID,))
    conn.commit()
    print(f"成功删除银行卡: {cardID}")
    conn.close()

#银行卡挂失
# 要求用户输入银行卡密码确认
def report_loss(cardID, cardmima_input):
    conn = get_connection()
    cursor = conn.cursor()
    
    # 查询银行卡的密码
    cursor.execute('''SELECT cardmima FROM cardinfo WHERE cardID=%s''', (cardID,))
    result = cursor.fetchone()
    
    if result and result['cardmima'] == cardmima_input:
        # 密码正确，执行挂失操作
        cursor.execute('''UPDATE cardinfo SET IsReportLoss=%s WHERE cardID=%s''', ('是', cardID))
        conn.commit()
        print(f"银行卡 {cardID} 已挂失。")
    else:
        print("银行卡密码错误，挂失失败。")
    
    conn.close()


# 银行卡解挂
# 要求用户输入银行卡密码确认
def unreport_loss(cardID, cardmima_input):
    conn = get_connection()
    cursor = conn.cursor()
    
    # 查询银行卡的密码和挂失状态
    cursor.execute('''SELECT cardmima, IsReportLoss FROM cardinfo WHERE cardID=%s''', (cardID,))
    result = cursor.fetchone()
    
    if result:
        cardmima, is_report_loss = result['cardmima'], result['IsReportLoss']
        
        # 检查银行卡是否处于挂失状态
        if is_report_loss == '否':
            print(f"银行卡 {cardID} 当前没有挂失，无需解挂。")
        else:
            # 验证密码是否正确
            if cardmima == cardmima_input:
                # 密码正确，执行解挂操作
                cursor.execute('''UPDATE cardinfo SET IsReportLoss=%s WHERE cardID=%s''', ('否', cardID))
                conn.commit()
                print(f"银行卡 {cardID} 已解挂。")
            else:
                print("银行卡密码错误，解挂失败。")
    
    else:
        print(f"银行卡 {cardID} 不存在。")
    
    conn.close()



# 1.4 修改银行卡密码
# 用户需要输入原密码和二次确认新密码
def change_card_password(cardID, old_password, new_password, confirm_new_password):
    conn = get_connection()
    cursor = conn.cursor()
    
    # 查询银行卡的密码和挂失状态
    cursor.execute('''SELECT cardmima, IsReportLoss FROM cardinfo WHERE cardID=%s''', (cardID,))
    result = cursor.fetchone()
    
    if result:
        # 检查银行卡是否已挂失
        if result['IsReportLoss'] == 'Yes':
            print(f"银行卡 {cardID} 已挂失，无法修改密码。")
        else:
            # 检查原密码是否正确
            if result['cardmima'] == old_password:
                # 检查新密码与确认密码是否一致
                if new_password == confirm_new_password:
                    cursor.execute('''UPDATE cardinfo SET cardmima=%s WHERE cardID=%s''', (new_password, cardID))
                    conn.commit()
                    print(f"银行卡 {cardID} 密码已修改。")
                else:
                    print("新密码与确认密码不一致，请重新输入。")
            else:
                print("原密码错误，密码修改失败。")
    else:
        print(f"银行卡 {cardID} 不存在，密码修改失败。")
    
    conn.close()

# 查询银行卡是否已挂失
def check_loss_status(cardID):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('''SELECT IsReportLoss FROM cardinfo WHERE cardID=%s''', (cardID,))
    result = cursor.fetchone()
    conn.close()
    if result:
        return result['IsReportLoss']
    else:
        return None

# 导出银行卡全部信息到CSV文件
def export_bankcards_to_csv(file_path="D:\\UNIVERSITY WORK\\大二下\\DBkeshe\\bank_system\\银行卡管理.csv"):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('''SELECT * FROM cardinfo''')
    rows = cursor.fetchall()
    conn.close()

    # 写入CSV文件
    with open(file_path, mode='w', newline='', encoding='utf-8') as file:
        writer = csv.writer(file)
        # 写入表头
        writer.writerow([
            '卡号', '货币类型', '储蓄类型', '开户日期', '开户金额', '余额', '银行卡密码', '是否挂失', '客户ID'
        ])
        # 写入数据
        for row in rows:
            writer.writerow([
                row['cardID'], row['curID'], row['savingID'], row['openDate'], row['openMoney'],
                row['balance'], row['cardmima'], row['IsReportLoss'], row['customerID']
            ])
    print(f"银行卡信息已成功导出到文件: {file_path}")





# 1.3 存款管理
# 添加存款记录
def add_deposit(savingName, descrip, cardID=None):
    conn = get_connection()
    cursor = conn.cursor()
    # 查询当前最大savingID
    cursor.execute('SELECT MAX(savingID) as maxid FROM deposit')
    row = cursor.fetchone()
    maxid = row['maxid'] if row and row['maxid'] is not None else 8
    new_savingID = max(maxid + 1, 9)
    cursor.execute('''INSERT INTO deposit (savingID, savingName, descrip) VALUES (%s, %s, %s)''', (new_savingID, savingName, descrip))
    conn.commit()
    print(f"成功添加存款记录: {savingName}, savingID={new_savingID}")
    conn.close()


# 查询存款记录
def view_all_deposits():
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('''SELECT * FROM deposit''')
    rows = cursor.fetchall()
    conn.close()
    print("所有存款信息:")
    for row in rows:
        print(row)
    return rows

# 更新存款记录
def update_deposit(savingID, savingName=None, descrip=None, cardID=None):
    conn = get_connection()
    cursor = conn.cursor()
    
    # 构造更新 SQL 语句
    update_fields = []
    params = []
    if savingName:
         cursor.execute('''UPDATE deposit SET savingName=%s WHERE savingID=%s''', (savingName, savingID))
    if descrip:
        cursor.execute('''UPDATE deposit SET descrip=%s WHERE savingID=%s''', (descrip, savingID))
    if cardID:
        cursor.execute('''UPDATE deposit SET cardID=%s WHERE savingID = %s''',(cardID,savingID))
        params.append(cardID) 
    conn.commit()
    print(f"成功更新存款记录: {savingID}")
    conn.close()

# 删除存款记录
def delete_deposit(savingID):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("DELETE FROM deposit WHERE savingID = %s", (savingID,))
    conn.commit()
    print(f"成功删除存款记录: {savingID}")
    conn.close()

# 导出存款全部信息到CSV文件
def export_deposits_to_csv(file_path="D:\\UNIVERSITY WORK\\大二下\\DBkeshe\\bank_system\\存款管理.csv"):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('''SELECT * FROM deposit''')
    rows = cursor.fetchall()
    conn.close()

    # 写入CSV文件
    with open(file_path, mode='w', newline='', encoding='utf-8') as file:
        writer = csv.writer(file)
        # 写入表头
        writer.writerow([
            '存款ID', '存款名称', '描述', '银行卡号'
        ])
        # 写入数据
        for row in rows:
            writer.writerow([
                row['savingID'], row['savingName'], row['descrip'], row['cardID']
            ])
    print(f"存款信息已成功导出到文件: {file_path}")



# 1.4 交易管理
# 添加交易记录
def add_transaction(tradeDate, tradeType, cardID, tradeMoney, remark):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('''INSERT INTO tradeinfo (tradeDate, tradeType, cardID, tradeMoney, remark)
                      VALUES (%s, %s, %s, %s, %s)''', (tradeDate, tradeType, cardID, tradeMoney, remark))
    conn.commit()
    conn.close()

# 查询所有交易记录
def view_all_transactions():
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('''SELECT * FROM tradeinfo''')
    rows = cursor.fetchall()
    conn.close()
    return rows

# 删除交易记录
def delete_transaction(tradeID):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('''DELETE FROM tradeinfo WHERE tradeID=%s''', (tradeID,))
    conn.commit()
    conn.close()

# 导出交易全部信息到CSV文件
def export_transactions_to_csv(file_path="D:\\UNIVERSITY WORK\\大二下\\DBkeshe\\bank_system\\交易管理.csv"):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('''SELECT * FROM tradeinfo''')
    rows = cursor.fetchall()
    conn.close()

    # 写入CSV文件
    with open(file_path, mode='w', newline='', encoding='utf-8') as file:
        writer = csv.writer(file)
        # 写入表头
        writer.writerow([
            '交易ID', '交易日期', '交易类型', '银行卡号', '交易金额', '备注'
        ])
        # 写入数据
        for row in rows:
            writer.writerow([
                row['tradeID'], row['tradeDate'], row['tradeType'], row['cardID'],
                row['tradeMoney'], row['remark']
            ])
    print(f"交易信息已成功导出到文件: {file_path}")





#二、客户模块
#2.1客户存款
def customer_deposit():
    """
    客户存款功能
    """
    conn = get_connection()
    cursor = conn.cursor()

    try:
        # 客户输入卡号和存款金额
        cardID = input("请输入您的银行卡号: ")
        amount = Decimal(input("请输入存款金额: "))
        remark = input("请输入交易备注(可选): ")

        if amount <= 0:
            print("\033[91m存款金额必须大于0\033[0m")
            return

        # 开始事务
        conn.begin()

        # 查询银行卡信息
        cursor.execute('''SELECT balance, IsReportLoss FROM cardinfo WHERE cardID=%s''', (cardID,))
        card_info = cursor.fetchone()

        if not card_info:
            print("\033[91m银行卡不存在\033[0m")
            return

        if card_info['IsReportLoss'] == 'Yes':
            print("\033[91m银行卡已挂失，无法存款\033[0m")
            return

        # 计算新余额
        new_balance = card_info['balance'] + amount

        # 更新余额
        cursor.execute('''UPDATE cardinfo SET balance=%s WHERE cardID=%s''', (new_balance, cardID))

        # 记录交易信息
        trade_date = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        cursor.execute('''INSERT INTO tradeinfo 
                         (cardID, tradeDate, tradeType, tradeMoney, remark)
                         VALUES (%s, %s, '存入', %s, %s)''',
                      (cardID, trade_date, amount, remark))

        # 提交事务
        conn.commit()
        print(f"\033[92m存款成功! 当前余额: {new_balance:.2f}元\033[0m")

    except ValueError:
        print("\033[91m请输入有效的金额\033[0m")
    except Exception as e:
        # 回滚事务
        conn.rollback()
        print(f"\033[91m存款过程中出错: {e}\033[0m")
    finally:
        conn.close()

#2.2客户取款
def customer_withdraw():
    """
    客户取款功能
    """
    conn = get_connection()
    cursor = conn.cursor()

    try:
        # 客户输入卡号、密码和取款金额
        cardID = input("请输入您的银行卡号: ")
        password = getpass.getpass("请输入您的银行卡密码: ")
        amount = Decimal(input("请输入取款金额: "))
        remark = input("请输入交易备注(可选): ") or "ATM取款"

        if amount <= 0:
            print("\033[91m取款金额必须大于0\033[0m")
            return

        # 开始事务
        conn.begin()

        # 查询银行卡信息
        cursor.execute('''SELECT balance, IsReportLoss, cardmima FROM cardinfo WHERE cardID=%s''', (cardID,))
        card_info = cursor.fetchone()

        if not card_info:
            print("\033[91m银行卡不存在\033[0m")
            return

        if card_info['IsReportLoss'] == 'Yes':
            print("\033[91m银行卡已挂失，无法取款\033[0m")
            return

        if card_info['cardmima'] != password:
            print("\033[91m密码错误\033[0m")
            return

        if card_info['balance'] < amount:
            print("\033[91m余额不足\033[0m")
            return

        # 计算新余额
        new_balance = card_info['balance'] - amount

        # 更新余额
        cursor.execute('''UPDATE cardinfo SET balance=%s WHERE cardID=%s''', (new_balance, cardID))

        # 记录交易信息
        trade_date = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        cursor.execute('''INSERT INTO tradeinfo 
                         (cardID, tradeDate, tradeType, tradeMoney, remark)
                         VALUES (%s, %s, '支取', %s, %s)''',
                      (cardID, trade_date, amount, remark))

        # 提交事务
        conn.commit()
        print(f"\033[92m取款成功! 当前余额: {new_balance:.2f}元\033[0m")

    except ValueError:
        print("\033[91m请输入有效的金额\033[0m")
    except Exception as e:
        # 回滚事务
        conn.rollback()
        print(f"\033[91m取款过程中出错: {e}\033[0m")
    finally:
        conn.close()


#2.3客户转账
def customer_transfer():
    """
    客户转账功能
    """
    conn = get_connection()
    cursor = conn.cursor()

    try:
        # 客户输入转出卡信息
        from_card = input("请输入转出卡号: ")
        password = getpass.getpass("请输入转出卡密码: ")
        
        # 验证转出卡 - 确保查询的字段名与数据库一致
        cursor.execute('''SELECT balance as balance, cardmima, IsReportLoss FROM cardinfo 
                          WHERE cardID=%s FOR UPDATE''', (from_card,))
        from_acc = cursor.fetchone()

        if not from_acc:
            print("\033[91m转出卡不存在\033[0m")
            return
        if from_acc['IsReportLoss'] == 'Yes':
            print("\033[91m转出卡已挂失\033[0m")
            return
        if from_acc['cardmima'] != password:
            print("\033[91m密码错误\033[0m")
            return

        # 客户输入转入卡信息
        to_card = input("请输入转入卡号: ")
        if from_card == to_card:
            print("\033[91m不能给自己转账\033[0m")
            return

        # 验证转入卡 - 确保查询的字段名与数据库一致
        cursor.execute('''SELECT balance as balance, IsReportLoss FROM cardinfo 
                          WHERE cardID=%s FOR UPDATE''', (to_card,))
        to_acc = cursor.fetchone()

        if not to_acc:
            print("\033[91m转入卡不存在\033[0m")
            return
        if to_acc['IsReportLoss'] == 'Yes':
            print("\033[91m转入卡已挂失\033[0m")
            return

        # 输入转账金额
        try:
            amount = Decimal(input("请输入转账金额: "))
            if amount <= 0:
                print("\033[91m转账金额必须大于0\033[0m")
                return
        except:
            print("\033[91m请输入有效的金额\033[0m")
            return

        # 检查余额
        if Decimal(from_acc['balance']) < amount:
            print("\033[91m余额不足\033[0m")
            return

        remark = input("请输入转账备注(可选): ") or "转账"

        # 开始事务
        conn.begin()

        # 计算新余额
        new_from_balance = Decimal(from_acc['balance']) - amount
        new_to_balance = Decimal(to_acc['balance']) + amount

        # 更新余额
        cursor.execute('''UPDATE cardinfo SET balance=%s WHERE cardID=%s''', 
                      (str(new_from_balance), from_card))
        cursor.execute('''UPDATE cardinfo SET balance=%s WHERE cardID=%s''', 
                      (str(new_to_balance), to_card))

        # 记录交易
        trade_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        cursor.execute('''INSERT INTO tradeinfo 
                         (cardID, tradeDate, tradeType, tradeMoney, remark)
                         VALUES (%s, %s, '转账支出', %s, %s)''',
                      (from_card, trade_time, str(amount), f"转至{to_card}:{remark}"))
        cursor.execute('''INSERT INTO tradeinfo 
                         (cardID, tradeDate, tradeType, tradeMoney, remark)
                         VALUES (%s, %s, '转账收入', %s, %s)''',
                      (to_card, trade_time, str(amount), f"来自{from_card}:{remark}"))

        # 提交事务
        conn.commit()
        print(f"\033[92m转账成功!\033[0m")
        print(f"转出卡: {from_card} 余额: {new_from_balance:.2f}")
        print(f"转入卡: {to_card} 余额: {new_to_balance:.2f}")

    except Exception as e:
        # 回滚事务
        conn.rollback()
        print(f"\033[91m转账过程中出错: {e}\033[0m")
        import traceback
        traceback.print_exc()  # 打印完整的错误堆栈
    finally:
        conn.close()

#2.4客户查询余额
def customer_check_balance():
    """
    客户查询余额功能
    """
    conn = get_connection()
    cursor = conn.cursor()

    try:
        # 客户输入卡号和密码
        cardID = input("请输入您的银行卡号: ").strip()
        password = getpass.getpass("请输入您的银行卡密码: ").strip()
        # 查询余额
        cursor.execute('''SELECT balance, IsReportLoss FROM cardinfo 
                         WHERE cardID=%s AND cardmima=%s''', (cardID, password))
        result = cursor.fetchone()

        if result:
            if result['IsReportLoss'] == 'Yes':
                print("\033[91m该银行卡已挂失，无法查询余额\033[0m")
            else:
                print("\n" + "=" * 40)
                print(f"\033[94m卡号: {cardID}\033[0m")
                print(f"\033[94m当前余额: ￥{result['balance']:.2f}\033[0m")
                print("=" * 40 + "\n")
        else:
            print("\033[91m卡号或密码错误，请重新输入!\033[0m")

    except Exception as e:
        print(f"\033[91m查询过程中出错: {e}\033[0m")
    finally:
        conn.close()

def customer_check_balance_web(cardID, password):
    """
    Web端客户查询余额功能
    """
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute('''SELECT balance, IsReportLoss FROM cardinfo 
                         WHERE cardID=%s AND cardmima=%s''', (cardID, password))
        result = cursor.fetchone()
        if result:
            if result['IsReportLoss'] == 'Yes':
                return "该银行卡已挂失，无法查询余额"
            else:
                return result['balance']
        else:
            return "卡号或密码错误"
    except Exception as e:
        return f"查询过程中出错: {e}"
    finally:
        conn.close()

#2.5客户挂失
def customer_report_loss():
    """
    客户挂失功能
    """
    conn = get_connection()
    cursor = conn.cursor()

    try:
        # 客户输入卡号和密码
        cardID = input("请输入要挂失的银行卡号: ").strip()
        password = getpass.getpass("请输入银行卡密码: ").strip()

        # 确认挂失
        confirm = input("确认要挂失此卡吗？(Y/N): ").strip().upper()
        if confirm != 'Y':
            print("\033[93m挂失操作已取消\033[0m")
            return

        # 验证卡号和密码
        cursor.execute('''SELECT cardID, IsReportLoss FROM cardinfo 
                         WHERE cardID=%s AND cardmima=%s''', (cardID, password))
        card_info = cursor.fetchone()

        if not card_info:
            print("\033[91m卡号或密码错误，挂失失败!\033[0m")
            return

        if card_info['IsReportLoss'] == 'Yes':
            print("\033[93m该卡已经处于挂失状态，无需重复挂失!\033[0m")
            return

        # 执行挂失
        cursor.execute('''UPDATE cardinfo SET IsReportLoss='Yes' WHERE cardID=%s''', (cardID,))
        conn.commit()

        print("\n" + "=" * 40)
        print(f"\033[91m卡号 {cardID} 挂失成功！\033[0m")
        print("\033[91m重要提示：\033[0m")
        print("\033[91m挂失后此卡将无法进行任何交易\033[0m")
        print("\033[91m请尽快携带身份证到银行网点办理补卡手续\033[0m")
        print("=" * 40 + "\n")

    except Exception as e:
        conn.rollback()
        print(f"\033[91m挂失过程中出错: {e}\033[0m")
    finally:
        conn.close()

#2.6客户解挂
def customer_cancel_loss():
    """
    客户解挂功能
    """
    conn = get_connection()
    cursor = conn.cursor()

    try:
        # 客户输入卡号和密码
        cardID = input("请输入要解挂的银行卡号: ").strip()
        password = getpass.getpass("请输入银行卡密码: ").strip()

        # 确认解挂
        confirm = input("确认要解除此卡的挂失状态吗？(Y/N): ").strip().upper()
        if confirm != 'Y':
            print("\033[93m解挂操作已取消\033[0m")
            return

        # 验证卡号和密码
        cursor.execute('''SELECT cardID, IsReportLoss FROM cardinfo 
                         WHERE cardID=%s AND cardmima=%s''', (cardID, password))
        card_info = cursor.fetchone()

        if not card_info:
            print("\033[91m卡号或密码错误，解挂失败!\033[0m")
            return

        if card_info['IsReportLoss'] == 'No':
            print("\033[93m该卡未处于挂失状态，无需解挂!\033[0m")
            return

        # 执行解挂
        cursor.execute('''UPDATE cardinfo SET IsReportLoss='No' WHERE cardID=%s''', (cardID,))
        conn.commit()

        print("\n" + "=" * 40)
        print(f"\033[92m卡号 {cardID} 解挂成功！\033[0m")
        print("\033[92m您的银行卡已恢复正常使用状态\033[0m")
        print("=" * 40 + "\n")

    except Exception as e:
        conn.rollback()
        print(f"\033[91m解挂过程中出错: {e}\033[0m")
    finally:
        conn.close()


#2.7客户更改密码
def customer_change_password():
    """
    客户更改密码功能
    """
    conn = get_connection()
    cursor = conn.cursor()

    try:
        # 客户输入卡号和密码
        cardID = input("请输入银行卡号: ").strip()
        old_password = getpass.getpass("请输入当前密码: ").strip()
        new_password = getpass.getpass("请输入新密码(6位数字): ").strip()
        confirm_password = getpass.getpass("请再次输入新密码: ").strip()


        if len(old_password) != 6 or not old_password.isdigit():
            print("\033[91m当前密码格式错误，请输入6位数字!\033[0m")
            return

        if len(new_password) != 6 or not new_password.isdigit():
            print("\033[91m新密码格式错误，请输入6位数字!\033[0m")
            return
        
        
        # 验证卡号和原密码
        cursor.execute('''SELECT cardID, IsReportLoss FROM cardinfo 
                         WHERE cardID=%s AND cardmima=%s''', (cardID, old_password))
        card_info = cursor.fetchone()

        if not card_info:
            print("\033[91m卡号或原密码错误，修改失败!\033[0m")
            return

        if card_info['IsReportLoss'] == 'Yes':
            print("\033[91m该卡已挂失，请先解挂再修改密码!\033[0m")
            return

        # 执行密码修改
        cursor.execute('''UPDATE cardinfo SET cardmima=%s WHERE cardID=%s''', 
                      (new_password, cardID))
        conn.commit()

        print("\n" + "=" * 40)
        print("\033[92m密码修改成功!\033[0m")
        print("\033[92m请妥善保管您的新密码\033[0m")
        print("=" * 40 + "\n")

    except Exception as e:
        conn.rollback()
        print(f"\033[91m密码修改过程中出错: {e}\033[0m")
    finally:
        conn.close()

def customer_deposit_web(cardID, cardmima, amount):
    """
    Web端客户存款功能
    """
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute('''SELECT cardmima, balance, IsReportLoss FROM cardinfo WHERE cardID=%s''', (cardID,))
        result = cursor.fetchone()
        if not result:
            return "卡号不存在"
        if result['IsReportLoss'] == 'Yes':
            return "该银行卡已挂失，无法存款"
        if result['cardmima'] != cardmima:
            return "密码错误"
        try:
            amount = Decimal(amount)
            if amount <= 0:
                return "存款金额必须大于零"
            if amount > 50000:
                return "单次存款最多为50000元"
        except:
            return "存款金额格式错误"
        new_balance = result['balance'] + amount
        cursor.execute('''UPDATE cardinfo SET balance=%s WHERE cardID=%s''', (new_balance, cardID))
        # 新增：插入交易记录
        trade_date = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        cursor.execute('''INSERT INTO tradeinfo (cardID, tradeDate, tradeType, tradeMoney, remark)
                          VALUES (%s, %s, %s, %s, %s)''',
                       (cardID, trade_date, '存入', amount, '客户自助存款'))
        conn.commit()
        return f"存款成功，当前余额：{new_balance}"
    except Exception as e:
        return f"存款过程中出错: {e}"
    finally:
        conn.close()

def customer_withdraw_web(cardID, cardmima, amount):
    """
    Web端客户取款功能
    """
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute('''SELECT cardmima, balance, IsReportLoss FROM cardinfo WHERE cardID=%s''', (cardID,))
        result = cursor.fetchone()
        if not result:
            return "卡号不存在"
        if result['IsReportLoss'] == 'Yes':
            return "该银行卡已挂失，无法取款"
        if result['cardmima'] != cardmima:
            return "密码错误"
        try:
            amount = Decimal(amount)
            if amount <= 0:
                return "取款金额必须大于零"
            if amount > 50000:
                return "单次取款最多为50000元"
        except:
            return "取款金额格式错误"
        if result['balance'] < amount:
            return "余额不足"
        new_balance = result['balance'] - amount
        cursor.execute('''UPDATE cardinfo SET balance=%s WHERE cardID=%s''', (new_balance, cardID))
        # 新增：插入交易记录
        trade_date = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        cursor.execute('''INSERT INTO tradeinfo (cardID, tradeDate, tradeType, tradeMoney, remark)
                          VALUES (%s, %s, %s, %s, %s)''',
                       (cardID, trade_date, '支取', amount, '客户自助取款'))
        conn.commit()
        return f"取款成功，当前余额：{new_balance}"
    except Exception as e:
        return f"取款过程中出错: {e}"
    finally:
        conn.close()

def customer_transfer_web(from_cardID, from_cardmima, to_cardID, amount):
    """
    Web端客户转账功能
    """
    from decimal import Decimal
    from datetime import datetime
    conn = get_connection()
    cursor = conn.cursor()
    try:
        # 检查转出卡
        cursor.execute('''SELECT cardmima, balance, IsReportLoss FROM cardinfo WHERE cardID=%s''', (from_cardID,))
        from_result = cursor.fetchone()
        if not from_result:
            return "转出卡号不存在"
        if from_result['IsReportLoss'] == 'Yes':
            return "转出银行卡已挂失，无法转账"
        if from_result['cardmima'] != from_cardmima:
            return "转出卡密码错误"
        # 检查转入卡
        cursor.execute('''SELECT cardID, IsReportLoss FROM cardinfo WHERE cardID=%s''', (to_cardID,))
        to_result = cursor.fetchone()
        if not to_result:
            return "转入卡号不存在"
        if to_result['IsReportLoss'] == 'Yes':
            return "转入银行卡已挂失，无法转账"
        try:
            amount = Decimal(amount)
            if amount <= 0:
                return "转账金额必须大于零"
        except:
            return "转账金额格式错误"
        if from_result['balance'] < amount:
            return "余额不足"
        # 扣款
        new_from_balance = from_result['balance'] - amount
        cursor.execute('''UPDATE cardinfo SET balance=%s WHERE cardID=%s''', (new_from_balance, from_cardID))
        # 加款
        cursor.execute('''UPDATE cardinfo SET balance=balance+%s WHERE cardID=%s''', (amount, to_cardID))
        # 插入交易流水
        trade_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        cursor.execute('''INSERT INTO tradeinfo (cardID, tradeDate, tradeType, tradeMoney, remark) VALUES (%s, %s, '转账支出', %s, %s)''',
            (from_cardID, trade_time, str(amount), f'转至{to_cardID}'))
        cursor.execute('''INSERT INTO tradeinfo (cardID, tradeDate, tradeType, tradeMoney, remark) VALUES (%s, %s, '转账收入', %s, %s)''',
            (to_cardID, trade_time, str(amount), f'来自{from_cardID}'))
        conn.commit()
        return f"转账成功，转出卡当前余额：{new_from_balance}"
    except Exception as e:
        return f"转账过程中出错: {e}"
    finally:
        conn.close()

def customer_change_password_web(cardID, old_password, new_password, confirm_password):
    """
    Web端客户更改密码功能，支持二次确认新密码
    """
    conn = get_connection()
    cursor = conn.cursor()
    try:
        if new_password != confirm_password:
            return "两次输入的新密码不一致"
        if len(new_password) != 6 or not new_password.isdigit():
            return "新密码格式错误，请输入6位数字"
        cursor.execute('''SELECT cardID, cardmima, IsReportLoss FROM cardinfo WHERE cardID=%s''', (cardID,))
        card_info = cursor.fetchone()
        if not card_info:
            return "卡号不存在"
        if card_info['IsReportLoss'] == 'Yes':
            return "该卡已挂失，请先解挂再修改密码"
        if card_info['cardmima'] != old_password:
            return "原密码错误"
        cursor.execute('''UPDATE cardinfo SET cardmima=%s WHERE cardID=%s''', (new_password, cardID))
        conn.commit()
        return "密码修改成功"
    except Exception as e:
        conn.rollback()
        return f"密码修改过程中出错: {e}"
    finally:
        conn.close()

def customer_report_loss_web(cardID, cardmima):
    """
    Web端客户挂失功能
    """
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute('''SELECT cardID, cardmima, IsReportLoss FROM cardinfo WHERE cardID=%s''', (cardID,))
        card_info = cursor.fetchone()
        if not card_info:
            return "卡号不存在"
        if card_info['IsReportLoss'] == 'Yes' or card_info['IsReportLoss'] == '是':
            return "卡已挂失"
        if card_info['cardmima'] != cardmima:
            return "密码错误"
        cursor.execute('''UPDATE cardinfo SET IsReportLoss='Yes' WHERE cardID=%s''', (cardID,))
        conn.commit()
        return "挂失成功"
    except Exception as e:
        conn.rollback()
        return f"挂失过程中出错: {e}"
    finally:
        conn.close()

def customer_cancel_loss_web(cardID, cardmima):
    """
    Web端客户解挂功能
    """
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute('''SELECT cardID, cardmima, IsReportLoss FROM cardinfo WHERE cardID=%s''', (cardID,))
        card_info = cursor.fetchone()
        if not card_info:
            return "卡号不存在"
        if card_info['IsReportLoss'] == 'No':
            return "该卡未处于挂失状态，无需解挂"
        if card_info['cardmima'] != cardmima:
            return "密码错误"
        cursor.execute('''UPDATE cardinfo SET IsReportLoss='No' WHERE cardID=%s''', (cardID,))
        conn.commit()
        return "解挂成功"
    except Exception as e:
        conn.rollback()
        return f"解挂过程中出错: {e}"
    finally:
        conn.close()

#主菜单
# 银行管理系统菜单代码

def display_admin_menu():
    print("\n" + "=" * 40)
    print("银行管理系统 - 管理员菜单".center(30))
    print("=" * 40)
    print("1. 客户管理")
    print("2. 银行卡管理")
    print("3. 存款管理")
    print("4. 交易管理")
    print("5. 返回上级菜单")
    print("=" * 40)

def display_customer_menu():
    print("\n" + "=" * 40)
    print("银行管理系统 - 客户菜单".center(30))
    print("=" * 40)
    print("1. 存款")
    print("2. 取款")
    print("3. 转账")
    print("4. 查询余额")
    print("5. 银行卡挂失")
    print("6. 银行卡解挂")
    print("7. 修改密码")
    print("8. 返回上级菜单")
    print("=" * 40)

def display_customer_management_menu():
    print("\n" + "=" * 40)
    print("客户管理".center(30))
    print("=" * 40)
    print("1. 添加客户")
    print("2. 查看所有客户")
    print("3. 更新客户信息")
    print("4. 删除客户")
    print("5. 客户存款")
    print("6. 客户取款")
    print("7. 导出客户数据")
    print("8. 返回上级菜单")
    print("=" * 40)

def display_bankcard_management_menu():
    print("\n" + "=" * 40)
    print("银行卡管理".center(30))
    print("=" * 40)
    print("1. 添加银行卡")
    print("2. 查看所有银行卡")
    print("3. 更新银行卡信息")
    print("4. 删除银行卡")
    print("5. 银行卡挂失")
    print("6. 银行卡解挂")
    print("7. 查询银行卡挂失状态")  
    print("8. 修改银行卡密码")
    print("9. 导出银行卡数据")
    print("10. 返回上级菜单")
    print("=" * 40)

def display_deposit_management_menu():
    print("\n" + "=" * 40)
    print("存款管理".center(30))
    print("=" * 40)
    print("1. 添加存款记录")
    print("2. 查看所有存款记录")
    print("3. 更新存款记录")
    print("4. 删除存款记录")
    print("5. 导出存款数据")
    print("6. 返回上级菜单")
    print("=" * 40)

def display_transaction_management_menu():
    print("\n" + "=" * 40)
    print("交易管理".center(30))
    print("=" * 40)
    print("1. 查看所有交易记录")
    print("2. 删除交易记录")
    print("3. 导出交易数据")
    print("4. 返回上级菜单")
    print("=" * 40)

def main_menu():
    while True:
        print("\n" + "=" * 40)
        print("银行管理系统主菜单".center(30))
        print("=" * 40)
        print("1. 管理员登录")
        print("2. 客户登录")
        print("3. 退出系统")
        print("=" * 40)
        
        choice = input("请选择操作(1-3): ")
        
        if choice == "1":
            admin_login()
        elif choice == "2":
            customer_menu()
        elif choice == "3":
            print("感谢使用银行管理系统，再见！")
            break
        else:
            print("无效的选择，请重新输入！")

def admin_login():
    # 这里可以添加管理员登录验证逻辑
    print("\n管理员登录成功！")
    admin_menu()

def admin_menu():
    while True:
        display_admin_menu()
        choice = input("请选择操作(1-5): ")
        
        if choice == "1":
            customer_management()
        elif choice == "2":
            bankcard_management()
        elif choice == "3":
            deposit_management()
        elif choice == "4":
            transaction_management()
        elif choice == "5":
            break
        else:
            print("无效的选择，请重新输入！")

def customer_management():
    while True:
        display_customer_management_menu()
        choice = input("请选择操作(1-8): ")
        
        if choice == "1":
            customerName = input("请输入客户姓名: ")
            PID = input("请输入身份证号: ")
            telephone = input("请输入电话号码: ")
            address = input("请输入地址: ")
            add_customer(customerName, PID, telephone, address)
        elif choice == "2":
            view_all_customers()
        elif choice == "3":
            customerID = input("请输入要更新的客户ID: ")
            customerName = input("请输入新的客户姓名(留空不修改): ")
            telephone = input("请输入新的电话号码(留空不修改): ")
            address = input("请输入新的地址(留空不修改): ")
            update_customer(customerID, customerName or None, telephone or None, address or None)
        elif choice == "4":
            customerID = input("请输入要删除的客户ID: ")
            delete_customer(customerID)
        elif choice == "5":
            deposit_to_customer()
        elif choice == "6":
            withdraw_from_customer()
        elif choice == "7":
            export_customers_to_csv()
        elif choice == "8":
            break
        else:
            print("无效的选择，请重新输入！")

def bankcard_management():
    while True:
        display_bankcard_management_menu()
        choice = input("请选择操作(1-10): ")
        
        if choice == "1":
            cardID = input("请输入银行卡号: ")
            curID = input("请输入货币类型: ")
            savingID = input("请输入储蓄类型: ")
            openDate = input("请输入开户日期(YYYY-MM-DD): ")
            openMoney = input("请输入开户金额: ")
            balance = input("请输入初始余额: ")
            cardmima = input("请输入银行卡密码: ")
            IsReportLoss = input("是否挂失(Yes/No): ")
            customerID = input("请输入客户ID: ")
            add_bankcard(curID, savingID, openMoney, customerID)
        elif choice == "2":
            view_all_bankcards()

        elif choice == "3":
            cardID = input("请输入要删除的银行卡号: ")
            delete_bankcard(cardID)
        elif choice == "4":
            cardID = input("请输入要挂失的银行卡号: ")
            cardmima = input("请输入银行卡密码: ")
            report_loss(cardID, cardmima)
        elif choice == "5":
            cardID = input("请输入要解挂的银行卡号: ")
            cardmima = input("请输入银行卡密码: ")
            unreport_loss(cardID, cardmima)
        elif choice == "6":  # 新增的查询挂失状态功能
            cardID = input("请输入要查询的银行卡号: ")
            status = check_loss_status(cardID)
            if status is None:
                print(f"银行卡 {cardID} 不存在")
            else:
                print(f"银行卡 {cardID} 的挂失状态: {'已挂失' if status == '是' else '未挂失'}")
        elif choice == "7":
            cardID = input("请输入银行卡号: ")
            old_password = input("请输入原密码: ")
            new_password = input("请输入新密码: ")
            confirm_new_password = input("请再次输入新密码: ")
            change_card_password(cardID, old_password, new_password, confirm_new_password)
        elif choice == "8":
            export_bankcards_to_csv()
        elif choice == "9":
            break
        else:
            print("无效的选择，请重新输入！")

def deposit_management():
    while True:
        display_deposit_management_menu()
        choice = input("请选择操作(1-6): ")
        
        if choice == "1":
            savingName = input("请输入存款名称: ")
            descrip = input("请输入存款描述: ")
            cardID = input("请输入关联的银行卡号: ")
            add_deposit(savingName, descrip, cardID)
        elif choice == "2":
            view_all_deposits()
        elif choice == "3":
            savingID = input("请输入要更新的存款ID: ")
            savingName = input("请输入新的存款名称(留空不修改): ")
            descrip = input("请输入新的存款描述(留空不修改): ")
            cardID = input("请输入新的关联银行卡号(留空不修改): ")
            update_deposit(savingID, savingName or None, descrip or None, cardID or None)
        elif choice == "4":
            savingID = input("请输入要删除的存款ID: ")
            delete_deposit(savingID)
        elif choice == "5":
            export_deposits_to_csv()
        elif choice == "6":
            break
        else:
            print("无效的选择，请重新输入！")

def transaction_management():
    while True:
        display_transaction_management_menu()
        choice = input("请选择操作(1-4): ")
        
        if choice == "1":
            transactions = view_all_transactions()
            for trans in transactions:
                print(trans)
        elif choice == "2":
            tradeID = input("请输入要删除的交易ID: ")
            delete_transaction(tradeID)
        elif choice == "3":
            export_transactions_to_csv()
        elif choice == "4":
            break
        else:
            print("无效的选择，请重新输入！")

def customer_menu():
    while True:
        display_customer_menu()
        choice = input("请选择操作(1-8): ")
        
        if choice == "1":
            customer_deposit()
        elif choice == "2":
            customer_withdraw()
        elif choice == "3":
            customer_transfer()
        elif choice == "4":
            customer_check_balance()
        elif choice == "5":
            customer_report_loss()
        elif choice == "6":
            customer_cancel_loss()
        elif choice == "7":
            customer_change_password()
        elif choice == "8":
            break
        else:
            print("无效的选择，请重新输入！")

# 查询客户姓名通过银行卡号

def get_customer_name_by_cardID(cardID):
    """
    通过银行卡号查询客户姓名
    """
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('''SELECT u.customerName FROM userinfo u JOIN cardinfo c ON u.customerID = c.customerID WHERE c.cardID = %s''', (cardID,))
    result = cursor.fetchone()
    conn.close()
    if result:
        return result['customerName']
    else:
        return None

# 更新银行卡信息，支持全部字段

def update_bankcard(cardID, curID=None, savingID=None, cardmima=None, IsReportLoss=None):
    conn = get_connection()
    cursor = conn.cursor()
    update_fields = []
    params = []
    if curID is not None:
        update_fields.append('curID=%s')
        params.append(curID)
    if savingID is not None:
        update_fields.append('savingID=%s')
        params.append(savingID)
    if cardmima is not None:
        update_fields.append('cardmima=%s')
        params.append(cardmima)
    if IsReportLoss is not None:
        update_fields.append('IsReportLoss=%s')
        params.append(IsReportLoss)
    if update_fields:
        sql = f"UPDATE cardinfo SET {', '.join(update_fields)} WHERE cardID=%s"
        params.append(cardID)
        cursor.execute(sql, tuple(params))
        conn.commit()
    conn.close()

# 通用查询函数

def query_userinfo(accurate=None, fuzzy=None, range_query=None):
    conn = get_connection()
    cursor = conn.cursor()
    sql = "SELECT * FROM userinfo WHERE 1=1"
    params = []
    if accurate:
        for k, v in accurate.items():
            sql += f" AND {k}=%s"
            params.append(v)
    if fuzzy:
        for k, v in fuzzy.items():
            sql += f" AND {k} LIKE %s"
            params.append(f"%{v}%")
    if range_query:
        for k, (minv, maxv) in range_query.items():
            if minv:
                sql += f" AND {k}>=%s"
                params.append(minv)
            if maxv:
                sql += f" AND {k}<=%s"
                params.append(maxv)
    cursor.execute(sql, tuple(params))
    result = cursor.fetchall()
    conn.close()
    return result

def query_cardinfo(accurate=None, fuzzy=None, range_query=None):
    conn = get_connection()
    cursor = conn.cursor()
    sql = "SELECT * FROM cardinfo WHERE 1=1"
    params = []
    if accurate:
        for k, v in accurate.items():
            sql += f" AND {k}=%s"
            params.append(v)
    if fuzzy:
        for k, v in fuzzy.items():
            sql += f" AND {k} LIKE %s"
            params.append(f"%{v}%")
    if range_query:
        for k, (minv, maxv) in range_query.items():
            if minv:
                sql += f" AND {k}>=%s"
                params.append(minv)
            if maxv:
                sql += f" AND {k}<=%s"
                params.append(maxv)
    cursor.execute(sql, tuple(params))
    result = cursor.fetchall()
    conn.close()
    return result

def query_deposit(accurate=None, fuzzy=None, range_query=None):
    conn = get_connection()
    cursor = conn.cursor()
    sql = "SELECT * FROM deposit WHERE 1=1"
    params = []
    if accurate:
        for k, v in accurate.items():
            sql += f" AND {k}=%s"
            params.append(v)
    if fuzzy:
        for k, v in fuzzy.items():
            sql += f" AND {k} LIKE %s"
            params.append(f"%{v}%")
    if range_query:
        for k, (minv, maxv) in range_query.items():
            if minv:
                sql += f" AND {k}>=%s"
                params.append(minv)
            if maxv:
                sql += f" AND {k}<=%s"
                params.append(maxv)
    cursor.execute(sql, tuple(params))
    result = cursor.fetchall()
    conn.close()
    return result

if __name__ == "__main__":
    main_menu()